<?php

namespace app\backend\controller;

use think\Controller;
use think\Request;
use think\Db;
use PHPExcel_IOFactory;
use PHPExcel;
use app\backend\model\Article as ArticleModel;

class Excel extends Controller
{
  function __construct(){
    vendor("PHPExcel.PHPExcel.PHPExcel");
    vendor("PHPExcel.PHPExcel.Writer.Excel5");
    vendor("PHPExcel.PHPExcel.Writer.Excel2007");
    vendor("PHPExcel.PHPExcel.IOFactory");
  }

  public function ArticleOut(){
    $path = dirname(__FILE__);
    $objPHPExcel = new \PHPExcel();
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
    $res = db('articles')->select();

    // 设置表头信息
    $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1','id')
    ->setCellValue('B1', 'title')
    ->setCellValue('C1', 'cover_img')
    ->setCellValue('D1', 'category')
    ->setCellValue('E1', 'body')
    ->setCellValue('F1', 'read_total')
    ->setCellValue('G1', 'upvote_total')
    ->setCellValue('H1', 'reply_total')
    ->setCellValue('I1', 'user_id')
    ->setCellValue('J1', 'created_at')
    ->setCellValue('K1', 'updated_at');

     /*--------------开始从数据库提取信息插入Excel表中------------------*/
    $i = 2; 
    $count = count($res);
    // var_dump(json_encode($res));
    for ($i = 2; $i<=$count+1; $i++){
      $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $res[$i-2]['id']);
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $res[$i-2]['title']);
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $res[$i-2]['cover_img']);
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $res[$i-2]['category']);
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $res[$i-2]['body']);
      $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $res[$i-2]['read_total']);
      $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $res[$i-2]['upvote_total']);
      $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $res[$i-2]['reply_total']);
      $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $res[$i-2]['user_id']);
      $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $res[$i-2]['created_at']);
      $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $res[$i-2]['updated_at']);
    }
      $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);

     /*--------------下面是设置其他信息------------------*/
     $objPHPExcel->getActiveSheet()->setTitle('users');      //设置sheet的名称
     $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来
     $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
     $time = date("Y-m-d h:i:sa");
     header('Content-Disposition: attachment;filename="'.$time.'-文章信息.xls"');
     header('Content-Type: application/vnd.ms-excel');
     $PHPWriter->save("php://output"); //表示在$path路径下
  }
  public function UserOut(){
    $path = dirname(__FILE__);
    $objPHPExcel = new \PHPExcel();
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
    $res = db('users')->select();

    // 设置表头信息
    $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1','id')
    ->setCellValue('B1', 'inkey')
    ->setCellValue('C1', 'name')
    ->setCellValue('D1', 'avatar')
    ->setCellValue('E1', 'permission_id')
    ->setCellValue('F1', 'email')
    ->setCellValue('G1', 'password')
    ->setCellValue('H1', 'follow')
    ->setCellValue('I1', 'created_at')
    ->setCellValue('J1', 'updated_at');

     /*--------------开始从数据库提取信息插入Excel表中------------------*/
    $i = 2; 
    $count = count($res);
    // var_dump(json_encode($res));
    for ($i = 2; $i<=$count+1; $i++){
      $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $res[$i-2]['id']);
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $res[$i-2]['inkey']);
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $res[$i-2]['name']);
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $res[$i-2]['avatar']);
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $res[$i-2]['permission_id']);
      $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $res[$i-2]['email']);
      $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $res[$i-2]['password']);
      $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $res[$i-2]['follow']);
      $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $res[$i-2]['created_at']);
      $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $res[$i-2]['updated_at']);
    }
      $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);

     /*--------------下面是设置其他信息------------------*/
     $objPHPExcel->getActiveSheet()->setTitle('user');      //设置sheet的名称
     $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来
     $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
     $time = date("Y-m-d h:i:s");
     header('Content-Disposition: attachment;filename="'.$time.'-用户信息.xls"');
     header('Content-Type: application/vnd.ms-excel');
     $PHPWriter->save("php://output"); //表示在$path路径下
  }

  public function ArticleUpLoad(){

    // if (!empty($_FILES)) {
    //   import("@.ORG.UploadFile");
    //   $config=array(
    //       'allowExts'=>array('xlsx','xls'),
    //       'savePath'=>'./public/uploads/',
    //       'saveRule'=>'time',
    //   );
    //   $upload = new UploadFile($config);
    //   if (!$upload->upload()) {
    //       $this->error($upload->getErrorMsg());
    //   } else {
    //       $info = $upload->getUploadFileInfo();
    //   }
    //   $filepath=$info[0]['savepath'].$info[0]['savename'];



    $file = request()->file('excel');
    $info = $file->move(ROOT_PATH.'public/'.'uploads');
    if($info){
      $filepath = $info->getSavename();
      $filename = $info->getFilename();
    }
    // if(file_exists($_SERVER['DOCUMENT_ROOT'].'/tp5/public/robots.txt')){
    //   $v = 1;
    // }else{
    //   $v = 0;
    // }
    // return $v.'  '.$filepath.'   '.$_SERVER['DOCUMENT_ROOT'];
    // exit;
    $filepath = $_SERVER['DOCUMENT_ROOT'].'/tp5/public/uploads/'.$filepath;
    $objReader = PHPExcel_IOFactory::createReader('Excel5');
    $objPHPExcel = $objReader->load($filepath, $encode='utf-8');
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow(); //取得总行数
    $highestColumn = $sheet->getHighestColumn(); //取得总列数
    // $data = [];
    for($i = 3; $i <= $highestRow; $i++){
      $data['title'] = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getValue();
      $data['cover_img'] = $objPHPExcel->getActiveSheet()->getCell('C'.$i)->getValue();
      $data['category'] = $objPHPExcel->getActiveSheet()->getCell('D'.$i)->getValue();
      $data['body'] = $objPHPExcel->getActiveSheet()->getCell('E'.$i)->getValue();
      $data['read_total'] = $objPHPExcel->getActiveSheet()->getCell('F'.$i)->getValue();
      $data['upvote_total'] = $objPHPExcel->getActiveSheet()->getCell('G'.$i)->getValue();
      $data['reply_total'] = $objPHPExcel->getActiveSheet()->getCell('H'.$i)->getValue();
      $data['user_id'] = $objPHPExcel->getActiveSheet()->getCell('I'.$i)->getValue();
      $data['created_at'] = $objPHPExcel->getActiveSheet()->getCell('J'.$i)->getValue();
      $data['updated_at'] = $objPHPExcel->getActiveSheet()->getCell('K'.$i)->getValue();
      Db::table('think_articles')->insert($data);
    }
    if($this->success('导入成功！')){
      $v = 1;
    }else{
      $this->error('请选择上传文件');
    }
  // }
  }
}